Data Type Casting Functions and Operators

CAST

CAST(cast_operand AS cast_target)

The CAST operator can be used to explicitly cast one data type to another in the SELECT statement. A query list or a value expression in the WHERE clause can be cast to another data type.

Parameters:
  • cast_operand -- Declares the value to cast to a different data type.
  • cast_target -- Specifies the type to cast to.
Return type:

cast_target

Depending on the situation, data type can be automatically converted without suing the CAST operator. For details, see Implicit Type Conversion.

See CASTing a String to Date/Time Type regarding to convert the string of date/time type into date/time type.

The following table shows a summary of explicit type conversions (casts) using the CAST operator in CUBRID.

From \ To EN AN VC FC VB FB ENUM BLOB CLOB D T UT DT S MS SQ
EN Yes Yes Yes Yes No No No No No No No No No No No No
AN Yes Yes Yes Yes No No No No No No No No No No No No
VC Yes Yes Yes(*) Yes(*) Yes Yes Yes Yes Yes Yes Yes Yes Yes No No No
FC Yes Yes Yes(*) Yes(*) Yes Yes Yes Yes Yes Yes Yes Yes Yes No No No
VB No No Yes Yes Yes Yes No Yes Yes No No No No No No No
FB No No Yes Yes Yes Yes No Yes Yes No No No No No No No
ENUM No No Yes Yes No No Yes No No No No No No No No No
BLOB No No No No Yes Yes Yes Yes No No No No No No No No
CLOB No No Yes Yes No No Yes No Yes No No No No No No No
D No No Yes Yes No No No No No Yes No Yes Yes No No No
T No No Yes Yes No No No No No No Yes No No No No No
UT No No Yes Yes No No No No No Yes Yes Yes Yes No No No
DT No No Yes Yes No No No No No Yes Yes Yes Yes No No No
S No No No No No No No No No No No No No Yes Yes Yes
MS No No No No No No No No No No No No No Yes Yes Yes
SQ No No No No No No No No No No No No No Yes Yes Yes

(*): The CAST operation is allowed only when the value expression and the data type to be cast have the same character set.

  • Data Type Key
    • EN: Exact numeric data type (INTEGER, SMALLINT, BIGINT, NUMERIC, DECIMAL)
    • AN: Approximate numeric data type (FLOAT/REAL, DOUBLE)
    • VC: Variable-length character string (VARCHAR (n))
    • FC: Fixed-length character string (CHAR (n))
    • VB: Variable-length bit string (BIT VARYING (n))
    • FB: Fixed-length bit string (BIT (n))
    • ENUM: ENUM type
    • BLOB: Binary data that is stored outside DB
    • CLOB: String data that is stored inside DB
    • D: DATE
    • T: TIME
    • DT: DATETIME
    • UT: TIMESTAMP
    • S: SET
    • MS: MULTISET
    • SQ: LIST (= SEQUENCE)
--operation after casting character as INT type returns 2
SELECT (1+CAST ('1' AS INT));

  (1+ cast('1' as integer))
===========================
                          2

--cannot cast the string which is out of range as SMALLINT
SELECT (1+CAST('1234567890' AS SMALLINT));

ERROR: Cannot coerce value of domain "character" to domain "smallint".

--operation after casting returns 1+1234567890
SELECT (1+CAST('1234567890' AS INT));

 (1+ cast('1234567890' as integer))
====================================
                          1234567891

--'1234.567890' is casted to 1235 after rounding up
SELECT (1+CAST('1234.567890' AS INT));

 (1+ cast('1234.567890' as integer))
====================================
  1236

--'1234.567890' is casted to string containing only first 5 letters.
SELECT (CAST('1234.567890' AS CHAR(5)));

 ( cast('1234.567890' as char(5)))
====================================
  '1234.'

--numeric type can be casted to CHAR type only when enough length is specified
SELECT (CAST(1234.567890 AS CHAR(5)));

ERROR: Cannot coerce value of domain "numeric" to domain "character".

--numeric type can be casted to CHAR type only when enough length is specified
SELECT (CAST(1234.567890 AS CHAR(11)));

 ( cast(1234.567890 as char(11)))
====================================
  '1234.567890'

--numeric type can be casted to CHAR type only when enough length is specified
SELECT (CAST(1234.567890 AS VARCHAR));

 ( cast(1234.567890 as varchar))
====================================
  '1234.567890'

--string can be casted to time/date types only when its literal is correctly specified
SELECT (CAST('2008-12-25 10:30:20' AS TIMESTAMP));

 ( cast('2008-12-25 10:30:20' as timestamp))
=============================================
  10:30:20 AM 12/25/2008

SELECT (CAST('10:30:20' AS TIME));

 ( cast('10:30:20' as time))
==================================================
  10:30:20 AM

--string can be casted to TIME type when its literal is same as TIME's.
SELECT (CAST('2008-12-25 10:30:20' AS TIME));

 ( cast('2008-12-25 10:30:20' as time))
========================================
  10:30:20 AM

--string can be casted to TIME type after specifying its type of the string
SELECT (CAST(TIMESTAMP'2008-12-25 10:30:20' AS TIME));

 ( cast(timestamp '2008-12-25 10:30:20' as time))
==================================================
  10:30:20 AM

SELECT CAST('abcde' AS BLOB);

 cast('abcde' as blob)
======================
file:/home1/user1/db/tdb/lob/ces_743/ces_temp.00001283232024309172_1342

SELECT CAST(B'11010000' as varchar(10));

  cast(B'11010000' as varchar(10))
====================================
  'd0'

SELECT CAST('1A' AS BLOB);

 cast('1A' as bit(16))
=================================
  X'1a00'

Note

  • CAST is allowed only between data types having the same character set.
  • If you cast an approximate data type(FLOAT, DOUBLE) to integer type, the number is rounded to zero decimal places.
  • If you cast an exact numeric data type(NUMERIC) to integer type, the number is rounded to zero decimal places.
  • If you cast a numeric data type to string character type, it should be longer than the length of significant figures + decimal point. An error occurs otherwise.
  • If you cast a character string type A to a character string type B, B should be longer than the A. The end of character string is truncated otherwise.
  • If you cast a character string type A to a date-time date type B, it is converted only when literal of A and B type match one another. An error occurs otherwise.
  • You must explicitly do type casting for numeric data stored in a character string so that an arithmetic operation can be performed.

DATE_FORMAT

DATE_FORMAT(date, format)

The DATE_FORMAT function converts the value of strings with DATE format ('YYYY-MM-DD' or 'MM/DD/YYYY') or that of date/time data type (DATE, TIMESTAMP, DATETIME) to specified date/time format and then return the value with the VARCHAR data type. For the format parameter to assign, refer to Date/Time Format 2 table of the DATE_FORMAT(). The Date/Time Format 2 table is used in DATE_FORMAT(), TIME_FORMAT(), and STR_TO_DATE() functions.

Parameters:
  • date -- A value of strings with the DATE format ('YYYY-MM-DD' or 'MM/DD/YYYY') or that of date/time data type (DATE, TIMESTAMP, DATETIME) can be specified .
  • format -- Specifies the output format. The format specifier starting with '%' is used.
Return type:

STRING

When the format argument is assigned, the string is interpreted according to the specified language. At that time, the language specified as the intl_date_lang system parameter is applied. If the intl_date_lang value is not set, the language specified when creating DB is applied.

For example, when the language is "de_DE" and the format is "%d %M %Y", the string "3 Oktober 2009" is interpreted as the DATE type of "2009-10-03". When the specified format argument does not correspond to the given string, an error is returned.

In the following Date/Time Format 2 table, the month/day, date, and AM/PM in characters are different by language.

Date/Time Format 2

format Value Meaning
%a Weekday, English abbreviation (Sun, ... , Sat)
%b Month, English abbreviation (Jan, ... , Dec)
%c Month (1, ... , 12)
%D Day of the month, English ordinal number (1st, 2nd, 3rd, ...)
%d Day of the month, two-digit number (01, ... , 31)
%e Day of the month (1, ... , 31)
%f Microseconds, three-digit number (000, ... , 999)
%H Hour, 24-hour based, number with at least two--digit (00, ... , 23, ... , 100, ... )
%h Hour, 12-hour based two-digit number (01, ... , 12)
%I Hour, 12-hour based two-digit number (01, ... , 12)
%i Minutes, two-digit number (00, ... , 59)
%j Day of year, three-digit number (001, ... , 366)
%k Hour, 24-hour based, number with at least one-digit (0, ... , 23, ... , 100, ... )
%l Hour, 12-hour based (1, ... , 12)
%M Month, English string (January, ... , December)
%m Month, two-digit number (01, ... , 12)
%p AM or PM
%r Time, 12-hour based, hour:minute:second (hh:mi:ss AM or hh:mi:ss PM)
%S Seconds, two-digit number (00, ... , 59)
%s Seconds, two-digit number (00, ... , 59)
%T Time, 24-hour based, hour:minute:second (hh:mi:ss)
%U Week, two-digit number, week number of the year with Sunday being the first day Week (00, ... , 53)
%u Week, two-digit number, week number of the year with Monday being the first day (00, ... , 53)
%V Week, two-digit number, week number of the year with Sunday being the first day Week (00, ... , 53) (Available to use in combination with %X)
%v Week, two-digit number, week number of the year with Monday being the first day (00, ... , 53) (Available to use in combination with %x)
%W Weekday, English string (Sunday, ... , Saturday)
%w Day of the week, number index (0=Sunday, ... , 6=Saturday)
%X Year, four-digit number calculated as the week number with Sunday being the first day of the week (0000, ... , 9999) (Available to use in combination with %V)
%x Year, four-digit number calculated as the week number with Monday being the first day of the week (0000, ... , 9999) (Available to use in combination with %v)
%Y Year, four-digit number (0001, ... , 9999)
%y Year, two-digit number (00, 01, ... , 99)
%% Output the special character "%" as a string
%x Output an arbitrary character x as a string out of English letters that are not used as format specifiers.

The following example shows the case when the system parameter intl_date_lang is "en_US".

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');

 date_format('2009-10-04 22:23:00', '%W %M %Y')
======================
  'Sunday October 2009'

SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');

 date_format('2007-10-04 22:23:00', '%H:%i:%s')
======================
  '22:23:00'

SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');

 date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j')
======================
  '4th 00 Thu 04 10 Oct 277'

SELECT DATE_FORMAT('1999-01-01', '%X %V');

 date_format('1999-01-01', '%X %V')
======================
  '1998 52'

The following example shows the case when the system parameter intl_date_lang is "de_DE".

SET SYSTEM PARAMETERS 'intl_date_lang="de_DE"';
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');

   date_format('2009-10-04 22:23:00', '%W %M %Y')
======================
  'Sonntag Oktober 2009'

SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s %p');

   date_format('2007-10-04 22:23:00', '%H:%i:%s %p')
======================
  '22:23:00 Nachm.'

SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');

   date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j')
======================
  '4 00 Do. 04 10 Okt 277'

Note

When the charset is ISO-8859-1, the language that can be changed by the system parameter intl_date_lang is "ko_KR" and "tr_TR" except "en_US". If the charset is UTF-8, it can be changed to any language supported by CUBRID. For details, see Note in the TO_CHAR()).

FORMAT

FORMAT(x, dec)

The FORMAT function displays the number x by using digit grouping symbol as a thousands separator, so that its format becomes '#,###,###.#####' and performs rounding after the decimal point symbol to express as many as dec digits after it. The return value is a VARCHAR type.

Parameters:
  • x -- An expression that returns a numeric value
  • dec -- the number of digits of fractional parts
Return type:

STRING

Thousands separator symbol and decimal point symbol is output in the format according to the specified language. The language is specified by the intl_number_lang system parameter. If the value of intl_number_lang is not set, the language specified when creating DB is applied.

For example, when the language is one of the European languages, such as "de_DE" or "fr_FR", "." is interpreted as the thousands separator and "," as the decimal point symbol (see Default output of number by language of the TO_CHAR()).

The following example shows command execution by setting the value of the intl_number_lang system parameter to "en_US".

SET SYSTEM PARAMETERS 'intl_number_lang="en_US"';
SELECT FORMAT(12000.123456,3), FORMAT(12000.123456,0);

  format(12000.123456, 3)   format(12000.123456, 0)
============================================
  '12,000.123'          '12,000'

The following example shows command execution on the database by setting the value of the intl_number_lang system parameter to "de_DE". In the number output format of most European countries, such as Germany and France, "." is the cipher identifier and "," is the decimal point symbol.

SET SYSTEM PARAMETERS 'intl_number_lang="de_DE"';
SELECT FORMAT(12000.123456,3), FORMAT(12000.123456,0);

   format(12000.123456, 3)   format(12000.123456, 0)
============================================
  '12.000,123'          '12.000'

STR_TO_DATE

STR_TO_DATE(string, format)

The STR_TO_DATE function converts the given character string to a date/time value by interpreting it according to the specified format and operates in the opposite way to the DATE_FORMAT() function. The return value is determined by the date/time part included in the character string and it is one of the DATETIME, DATE and TIME types.

Parameters:
  • string -- All character string types can be specified.
  • format -- Specifies the format to interpret the character string. You should use character strings including % for the format specifiers. See Date/Time Format 2 table of DATE_FORMAT() function.
Return type:

DATETIME, DATE, TIME

For the format argument to assign, see Date/Time Format 2 table of the DATE_FORMAT().

If string is invalid date/time value or format is invalid, it returns an error.

When the format argument is assigned, the string is interpreted according to the specified language. At that time, the language specified as the intl_date_lang system parameter is applied. If the intl_date_lang value is not set, the language specified when creating DB is applied.

For example, when the language is "de_DE" and the format is "%d %M %Y", the string "3 Oktober 2009" is interpreted as the DATE type of "2009-10-03". If the format argument does not correspond to the given string, an error is returned.

0 is not allowed in the argument value corresponding to year, month, and day; however, if 0 is inputted in every argument value corresponding to date and time, the value of DATE or DATETIME type that has 0 for every date and time value is returned as an exception. Note that operation in JDBC program is determined by the configuration of zeroDateTimeBehavior, connection URL property. For more information about zeroDateTimeBehavior, please see Configuration Connection.

The following example shows the case when the system parameter intl_date_lang is "en_US".

SET SYSTEM PARAMETERS 'intl_date_lang="en_US"';
SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');

 str_to_date('01,5,2013', '%d,%m,%Y')
=======================================
  05/01/2013

SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');

 str_to_date('May 1, 2013', '%M %d,%Y')
=========================================
  05/01/2013

SELECT STR_TO_DATE('13:30:17','%H:%i');

 str_to_date('13:30:17', '%H:%i')
========================================
  01:30:00 PM

SELECT STR_TO_DATE('09:30:17 PM','%r');

 str_to_date('09:30:17 PM', '%r')
=======================================
  09:30:17 PM

SELECT STR_TO_DATE('0,0,0000','%d,%m,%Y');

 str_to_date('0,0,0000', '%d,%m,%Y')
======================================
  00/00/0000

The following example shows the case when the system parameter intl_date_lang is "de_DE". The German Oktober is interpreted to 10.

SET SYSTEM PARAMETERS 'intl_date_lang="de_DE"';
SELECT STR_TO_DATE('3 Oktober 2009', '%d %M %Y');

   str_to_date('3 Oktober 2009', '%d %M %Y')
============================================
  10/03/2009

Note

When the charset is ISO-8859-1, the language that can be changed by the system parameter intl_date_lang is "ko_KR" and "tr_TR" except "en_US". If the charset is UTF-8, it can be changed to any language supported by CUBRID. For details, see Note in the TO_CHAR()).

TIME_FORMAT

TIME_FORMAT(time, format)

The TIME_FORMAT function converts the value of strings with TIME format ('HH-MI-SS) or that of date/time data type (DATE, TIMESTAMP, DATETIME) to specified date/time format and then return the value with the VARCHAR data type.

Parameters:
  • time -- A value of string with TIME (HH:MI:SS) or that of date/time data type (TIME, TIMESTAMP, DATETIME) can be specified.
  • format -- Specifies the output format. Use a string that contains '%' as a specifier. See the table Date/Time Format 2 of DATE_FORMAT() function.
Return type:

STRING

When the format argument is assigned, the time is output according to the specified language. At this time, the language specified as the intl_date_lang system parameter is applied. If intl_date_lang system parameter is not set, the language specified when creating DB is applied.

For example, when the language is set to "de_DE" and the format is "%h:%i:%s %p", "08:46:53 PM" is output as "08:46:53 Nachm.". When the format argument specified does not correspond to the given string, an error is returned.

The following example shows the case when the system parameter intl_date_lang is "en_US".

SET SYSTEM PARAMETERS 'intl_date_lang="en_US"';
SELECT TIME_FORMAT('22:23:00', '%H %i %s');

 time_format('22:23:00', '%H %i %s')
======================
  '22 23 00'

SELECT TIME_FORMAT('23:59:00', '%H %h %i %s %f');

 time_format('23:59:00', '%H %h %i %s %f')
======================
  '23 11 59 00 000'

SELECT SYSTIME, TIME_FORMAT(SYSTIME, '%p');

 SYS_TIME     time_format( SYS_TIME , '%p')
===================================
  08:46:53 PM  'PM'

The following example shows the case when the system parameter intl_date_lang is "de_DE".

SET SYSTEM PARAMETERS 'intl_date_lang="de_DE"';
SELECT SYSTIME, TIME_FORMAT(SYSTIME, '%p');

   SYS_TIME     time_format( SYS_TIME , '%p')
===================================
  08:46:53 PM  'Nachm.'

Note

When the charset is ISO-8859-1, the language that can be changed by the system parameter intl_date_lang is "ko_KR" and "tr_TR" except "en_US". If the charset is UTF-8, it can be changed to any language supported by CUBRID. For details, see Note in the TO_CHAR()).

TO_CHAR(date_time)

TO_CHAR(date_time[, format[, date_lang_string_literal]])

The TO_CHAR (date_time) function converts the value of date/time types (TIME, DATE, TIMESTAMP, DATETIME) to a string depending on the table Date/Time Format 1 and then returns the value. The type of the return value is VARCHAR.

Parameters:
  • date_time -- Specifies an expression that returns date-time type string. If the value is NULL, NULL is returned.
  • format -- Specifies a format of return value. If the value is NULL, NULL is returned.
  • date_lang_string_literal -- Specifies a language applied to a return value.
Return type:

STRING

When the format argument is specified, the date_time is output according to the specified language (see the Date/Time Format 1 table). A language is defined by the date_lang_string_literal. If date_lang_string_literal is omitted, the language specified by the intl_date_lang parameter is applied; if the value of intl_date_lang is not specified, the language specified when creating DB is applied.

For example, when the language is set to "de_DE" and the format is "HH:MI:SS:AM", "08:46:53 PM" is output as "08:46:53 Nachm.". When the format argument specified does not correspond to the given string, an error is returned.

When the format argument is omitted, the date_time is output as a string according to the default output format of the "en_US"(see the following table Default Date/Time Output Format for Each Language).

Note

The CUBRID_DATE_LANG environment used in earlier version of CUBRID 9.0 is no longer supported.

Default Date/Time Output Format for Each Language

LANG DATE TIME TIMESTAMP DATETIME
en_US 'MM/DD/YYYY' 'HH:MI:SS AM' 'HH:MI:SS AM MM/DD/YYYY' 'HH:MI:SS.FF AM MM/DD/YYYY'
de_DE 'DD.MM.YYYY' 'HH24:MI:SS' 'HH24:MI:SS DD.MM.YYYY' 'HH24:MI:SS.FF DD.MM.YYYY'
es_ES 'DD.MM.YYYY' 'HH24:MI:SS' 'HH24:MI:SS DD.MM.YYYY' 'HH24:MI:SS.FF DD.MM.YYYY'
fr_FR 'DD.MM.YYYY' 'HH24:MI:SS' 'HH24:MI:SS DD.MM.YYYY' 'HH24:MI:SS.FF DD.MM.YYYY'
it_IT 'DD.MM.YYYY' 'HH24:MI:SS' 'HH24:MI:SS DD.MM.YYYY' 'HH24:MI:SS.FF DD.MM.YYYY'
ja_JP 'YYYY/MM/DD' 'HH24:MI:SS' 'HH24:MI:SS YYYY/MM/DD' 'HH24:MI:SS.FF YYYY/MM/DD'
km_KH 'DD/MM/YYYY' 'HH24:MI:SS' 'HH24:MI:SS DD/MM/YYYY' 'HH24:MI:SS.FF DD/MM/YYYY'
ko_KR 'YYYY.MM.DD' 'HH24:MI:SS' 'HH24:MI:SS YYYY.MM.DD' 'HH24:MI:SS.FF YYYY.MM.DD'
tr_TR 'DD.MM.YYYY' 'HH24:MI:SS' 'HH24:MI:SS DD.MM.YYYY' 'HH24:MI:SS.FF DD.MM.YYYY'
vi_VN 'DD/MM/YYYY' 'HH24:MI:SS' 'HH24:MI:SS DD/MM/YYYY' 'HH24:MI:SS.FF DD/MM/YYYY'
zh_CN 'YYYY-MM-DD' 'HH24:MI:SS' 'HH24:MI:SS YYYY-MM-DD' 'HH24:MI:SS.FF YYYY-MM-DD'
ro_RO 'DD.MM.YYYY' 'HH24:MI:SS' 'HH24:MI:SS DD.MM.YYYY' 'HH24:MI:SS.FF DD.MM.YYYY'

Date/Time Format 1

Format Element Description
CC Century
YYYY , YY Year with 4 numbers, Year with 2 numbers
Q Quarter (1, 2, 3, 4; January - March = 1)
MM Month (01-12; January = 01) Note: MI represents the minute of hour.
MONTH Month in characters
MON Abbreviated month name
DD Day (1 - 31)
DAY Day of the week in characters
DY Abbreviated day of the week
D or d Day of the week in numbers (1 - 7)
AM or PM AM/PM
A.M. or P.M. AM/PM with periods
HH or HH12 Hour (1 -12)
HH24 Hour (0 - 23)
MI Minute (0 - 59)
SS Second (0 - 59)
FF Millisecond (0-999)
- / , . ; : "text" Punctuation and quotation marks are represented as they are in the result

Example of date_lang_string_literal

Format Element date_lang_string_literal
'en_US' 'ko_KR'
MONTH JANUARY 1월
MON JAN 1
DAY MONDAY 월요일
DY MON 월
Month January 1월
Mon Jan 1
Day Monday 월요일
Dy Mon 월
month january 1월
mon jan 1
day monday 월요일
Dy mon 월
AM AM 오전
Am Am 오전
am am 오전
A.M. A.M. 오후
A.m. A.m. 오전
a.m. a.m. 오전
PM PM 오후
Pm Pm 오후
pm pm 오후
P.M. P.M. 오후
P.m. P.m. 오후
p.m. p.m. 오후

Example of Format Digits of Return Value

Format Element en_US Digits | ko_KR Digits
MONTH(Month, month) 9 4
MON(Mon, mon) 3 2
DAY(Day, day) 9 6
DY(Dy, dy) 3 2
HH12, HH24 2 2
"text" The length of the text The length of the text
Other formats Same as the length of the format Same as the length of the format

The following example shows the query executed by setting the language and charset to "en_US.iso88591".

-- create database testdb en_US.iso88591

--creating a table having date/time type columns
CREATE TABLE datetime_tbl(a TIME, b DATE, c TIMESTAMP, d DATETIME);
INSERT INTO datetime_tbl VALUES(SYSTIME, SYSDATE, SYSTIMESTAMP, SYSDATETIME);

--selecting a VARCHAR type string from the data in the specified format
SELECT TO_CHAR(b, 'DD, DY , MON, YYYY') FROM datetime_tbl;

 to_char(b, 'DD, DY , MON, YYYY')
======================
'20, TUE , AUG, 2013'

SELECT TO_CHAR(c, 'HH24:MI, DD, MONTH, YYYY') FROM datetime_tbl;

 to_char(c, 'HH24:MI, DD, MONTH, YYYY')
======================
'17:00, 20, AUGUST   , 2013'

SELECT TO_CHAR(d, 'HH12:MI:SS:FF pm, YYYY-MM-DD-DAY') FROM datetime_tbl;

 to_char(d, 'HH12:MI:SS:FF pm, YYYY-MM-DD-DAY')
======================
'05:00:58:358 pm, 2013-08-20-TUESDAY  '

SELECT TO_CHAR(TIMESTAMP'2009-10-04 22:23:00', 'Day Month yyyy');

 to_char(timestamp '2009-10-04 22:23:00', 'Day Month yyyy')
======================
'Sunday    October   2009'

The following example shows an additional language parameter given to the TO_CHAR function in the database created above. When the charset is ISO-8859-1, setting the language parameter of the TO_CHAR function to "tr_TR" or "ko_KR" is allowed, but the other languages are not allowed. To use all languages by setting the language parameter of TO_CHAR, the charset when creating DB should be UTF-8.

SELECT TO_CHAR(TIMESTAMP'2009-10-04 22:23:00', 'Day Month yyyy','ko_KR');

   to_char(timestamp '2009-10-04 22:23:00', 'Day Month yyyy', 'ko_KR')
======================
  'Iryoil    10wol 2009'

SELECT TO_CHAR(TIMESTAMP'2009-10-04 22:23:00', 'Day Month yyyy','tr_TR');

   to_char(timestamp '2009-10-04 22:23:00', 'Day Month yyyy', 'tr_TR')
======================
  'Pazar     Ekim    2009'

Note

  • In the function that interprets the month/day in characters and AM/PM differently by language, if the charset is ISO-8859-1, the language can be changed to "ko_KR" or "tr_TR" only by using the intl_date_lang except "en_US" (see the above example). If the charset is UTF-8, the language can be changed to any language supported by CUBRID. By setting the intl_date_lang system parameter or by specifying the language parameter of the TO_CHAR function, the language can be changed to one of all the languages supported by CUBRID (see date_lang_string_literal of "Syntax" above). For a list of functions that interpret the date/time differently by language, see the description of the intl_date_lang system parameter.

    -- change date locale as "de_DE" and run the below query.
-- This case is failed because database locale, en_US's charset is ISO-8859-1
-- and 'de_DE' only supports UTF-8 charset.

SELECT TO_CHAR(TIMESTAMP'2009-10-04 22:23:00', 'Day Month yyyy','de_DE');

    ERROR: before ' , 'Day Month yyyy','de_DE'); '
Locales for language 'de_DE' are not available with charset 'iso8859-1'.

    The following example shows how to set the language parameter of the TO_CHAR function to "de_DE" when you created DB with the locale "en_US.utf8".

    SELECT TO_CHAR(TIMESTAMP'2009-10-04 22:23:00', 'Day Month yyyy','de_DE');

       to_char(timestamp '2009-10-04 22:23:00', 'Day Month yyyy', 'de_DE')
======================
  'Sonntag   Oktober 2009'

  • If the first argument is zerodate and the second argument has a literal like 'Month', 'Day', then TO_CHAR function returns NULL.

    SELECT TO_CHAR(timestamp '0000-00-00 00:00:00', 'Month Day YYYY');

    NULL

TO_CHAR(number)

TO_CHAR(number[, format[, number_lang_string_literal]])

The TO_CHAR function converts a numeric data type to a character string according to Number Format and returns it. The type of the return value is VARCHAR .

Parameters:
  • number -- Specifies an expression that returns numeric data type string. If the input value is NULL, NULL is returned. If the input value is character type, the character itself is returned.
  • format -- Specifies a format of return value. If format is not specified, all significant figures are returned as character string by default. If the value is NULL, NULL is returned.
  • number_lang_string_literal -- Specifies the language to be applied to the input value.
Return type:

STRING

If format argument is specified, number is converted into a character string according to a specified language. At this time, the language is defined by the number_lang_string_literal argument. If number_lang_string_literal is omitted, the language specified by intl_number_lang system parameter is applied; if intl_number_lang is not set, the language specified when creating DB is applied.

For example, if the language is one of the European languages, such as "de_DE" or "fr_FR", "." is printed out as a thousands separator and "," is printed out as a decimal point. If the format argument does not correspond to the given string, the function returns an error.

If format argument is omitted, number is converted into a character string according to the default format of a specified language(see the table Default Output of Number for Each Language).

Number Format

Format Element Example Description
9 9999 The number of 9's represents the number of significant figures to be returned. If the number of significant figures specified in the format is not sufficient, only the decimal part is rounded. If it is less than the number of digits in an integer, # is outputted. If the number of significant figures specified in the format is sufficient, the part preceding the integer part is filled with space characters and the decimal part is filled with 0.
0 0999 If the number of significant figures specified in the format is sufficient, the part preceding the integer part is filled with 0, not space characters before the value is returned.
S S9999 Outputs the negative/positive sign in the specified position. These signs can be used only at the beginning of character string.
C C9999 Returns the ISO currency code at the specified position.
, (comma) 9,999 Returns a comma (",") at the specified position. Multiple commas are allowed in the format.
. (decimal point) 9.999 Returns a decimal point (".") which distinguishes between a decimal and an at the specified position. Only one decimal point is allowed in the format. see the table, Default Output of Number for Each Language
EEEE 9.99EEEE Returns a scientific notation number.

Default Output of Number for Each Language

Language Locale Number of Digits Decimal Symbol Example of Number Usage
English en_US ,(comma) .(period) 123,456,789.012
German de_DE .(period) ,(comma) 123.456.789,012
Spanish es_ES .(period) ,(comma) 123.456.789,012
French fr_FR .(period) ,(comma) 123.456.789,012
Italian it_IT .(period) ,(comma) 123.456.789,012
Japanese ja_JP ,(comma) .(period) 123,456,789.012
Cambodian km_KH .(period) ,(comma) 123.456.789,012
Korean ko_KR ,(comma) .(period) 123,456,789.012
Turkish tr_TR .(period) ,(comma) 123.456.789,012
Vietnamese vi_VN .(period) ,(comma) 123.456.789,012
Chinese zh_CN ,(comma) .(period) 123,456,789.012
Romanian ro_RO .(period) ,(comma) 123.456.789,012

The following example shows execution of the database by the locale specified when creating DB to "en_US.utf8".

--selecting a string casted from a number in the specified format
SELECT TO_CHAR(12345,'S999999'), TO_CHAR(12345,'S099999');

  to_char(12345, 'S999999')   to_char(12345, 'S099999')
============================================
  ' +12345'             '+012345'

SELECT TO_CHAR(1234567,'9,999,999,999');

  to_char(1234567, '9,999,999,999')
======================
  '    1,234,567'

SELECT TO_CHAR(1234567,'9.999.999.999');

  to_char(1234567, '9.999.999.999')
======================
  '##############'

SELECT TO_CHAR(123.4567,'99'), TO_CHAR(123.4567,'999.99999'), TO_CHAR(123.4567,'99999.999');

  to_char(123.4567, '99')   to_char(123.4567, '999.99999')   to_char(123.4567, '99999.999')
==================================================================
  '##'                  '123.45670'           '  123.457'

The following example shows command execution by setting the value of the intl_number_lang system parameter to "de_DE". In the number output format of most European countries such as Germany and France, "." is the cipher identifier and "," is the decimal point symbol.

SET SYSTEM PARAMETERS 'intl_number_lang="de_DE"';

--selecting a string casted from a number in the specified format
SELECT TO_CHAR(12345,'S999999'), TO_CHAR(12345,'S099999');

  to_char(12345, 'S999999')   to_char(12345, 'S099999')
============================================
  ' +12345'             '+012345'

SELECT TO_CHAR(1234567,'9,999,999,999');

  to_char(1234567, '9,999,999,999')
======================
  '##############'

SELECT TO_CHAR(1234567,'9.999.999.999');

  to_char(1234567, '9.999.999.999')
======================
  '    1.234.567'

SELECT TO_CHAR(123.4567,'99'), TO_CHAR(123.4567,'999,99999'), TO_CHAR(123.4567,'99999,999');

  to_char(123.4567, '99')   to_char(123.4567, '999,99999')   to_char(123.4567, '99999,999')
==================================================================
  '##'                  '123,45670'           '  123,457'

SELECT TO_CHAR(123.4567,'99','en_US'), TO_CHAR(123.4567,'999.99999','en_US'), TO_CHAR(123.4567,'99999.999','en_US');

 to_char(123.4567, '99', 'en_US')   to_char(123.4567, '999.99999', 'en_US')   to_char(123.4567, '99999.999', 'en_US')
==========================================================
  '##'                  '123.45670'           '  123.457'

SELECT TO_CHAR(1.234567,'99.999EEEE','en_US'), TO_CHAR(1.234567,'99,999EEEE','de_DE'), to_char(123.4567);

  to_char(1.234567, '99.999EEEE', 'en_US')   to_char(1.234567, '99,999EEEE', 'de_DE')   to_char(123.4567)
==================================================================
  '1.235E+00'           '1,235E+00'           '123,4567'

TO_DATE

TO_DATE(string[, format[, date_lang_string_literal]])

The TO_DATE function interprets a character string based on the date format given as an argument, converts it to a DATE type value, and returns it. For the format, see Date/Time Format 1.

Parameters:
  • string -- Specifies an expression that returns character string. If the value is NULL, NULL is returned.
  • format -- Specifies a format of return value to be converted as DATE type. See Date/Time Format 1. If the value is NULL, NULL is returned.
  • date_lang_string_literal -- Specifies the language for the input value to be applied.
Return type:

DATE

When the format argument is specified, the string is interpreted according to the specified language. At this time, the language is set by date_lang_string_literal argument. If date_lang_string_literal argument is not set, the language is specified by the intl_date_lang system parameter; if the value of intl_date_lang is not set, the language is applied by the language specified when creating DB.

For example, when a language is "de_DE" and string is "12.mai.2012", and format is "DD.mon.YYYY", it is interpreted as May 12th, 2012.When the format parameter specified does not correspond to the given string, an error is returned.

When the format argument is omitted, string is interpreted as the CUBRID default format (refer to Recommended Format for Strings in Date/Time Type) and if it fails, string is interpreted as the language format (see the table Default Output Format of Language in the TO_CHAR()) by intl_date_lang. If the value of intl_date_lang is not set, the language is applied by the language specified when creating DB.

For example, when a language is "de_DE", the acceptable strings for DATE type are "MM/DD/YYYY", CUBRID default format and "DD.MM.YYYY", "de_DE" default format.

The following example shows the query executed by the locale specified when creating DB to "en_US.utf8".

--selecting a date type value casted from a string in the specified format

SELECT TO_DATE('12/25/2008');

 to_date('12/25/2008')
===============================================
  12/25/2008

SELECT TO_DATE('25/12/2008', 'DD/MM/YYYY');

 to_date('25/12/2008', 'DD/MM/YYYY')
===============================================
  12/25/2008

SELECT TO_DATE('081225', 'YYMMDD');

 to_date('081225', 'YYMMDD')
===============================================
  12/25/2008

SELECT TO_DATE('2008-12-25', 'YYYY-MM-DD');

 to_date('2008-12-25', 'YYYY-MM-DD')
===============================================
  12/25/2008

The following example shows the query executed when the system parameter intl_date_lang is "de_DE".

SET SYSTEM PARAMETERS 'intl_date_lang="de_DE"';
SELECT TO_DATE('25.12.2012');

   to_date('25.12.2012')
========================
   12/25/2012

SELECT TO_DATE('12/mai/2012','dd/mon/yyyy', 'de_DE');

   to_date('12/mai/2012', 'dd/mon/yyyy', 'de_DE')
=================================================
   05/12/2012

Note

When the charset is ISO-8859-1, the language that can be changed by the system parameter intl_date_lang is "ko_KR" and "tr_TR" except "en_US". If the charset is UTF-8, it can be changed to any language supported by CUBRID. For details, see Note in the TO_CHAR()).

TO_DATETIME

TO_DATETIME(string[, format[, date_lang_string_literal]])

The TO_DATETIME function interprets a character string based on the date-time format given as an argument, converts it to a DATETIME type value, and returns it. For the format, see Date/Time Format 1.

Parameters:
  • string -- Specifies an expression that returns character string. If the value is NULL, NULL is returned.
  • format -- Specifies a format of return value to be converted as DATETIME type. See the table, Date/Time Format 1. If the value is NULL, NULL is returned.
  • date_lang_string_literal -- Specifies the language for the input value to be applied.
Return type:

DATETIME

When the format argument is specified, the string is interpreted according to the specified language.

For example, when a language is "de_DE" and string is "12/mai/2012 12:10:00 Nachm." and format is "DD/MON/YYYY HH:MI:SS AM", it is interpreted as May 12th, 2012 12:10:00 PM. At this time, the language is set by date_lang_string_literal argument. If date_lang_string_literal argument is not set, the language is specified by the intl_date_lang system parameter; if the value of intl_date_lang is not set, the language is specified by the language specified when creating DB. When the format parameter specified does not correspond to the given string, an error is returned.

When the format argument is omitted, string is interpreted as the CUBRID default format (refer to Recommended Format for Strings in Date/Time Type) and if it fails, string is interpreted as the language format (see the table Default Output Format of Language in the TO_CHAR()) by intl_date_lang. If the value of intl_date_lang is not set, the language is applied by the language specified when creating DB.

For example, when a language is "de_DE", the acceptable strings for DATETIME type are "HH:MI:SS.FF AM MM/DD/YYYY", CUBRID default format and "HH24:MI:SS.FF DD.MM.YYYY", "de_DE" default format.

Note

The CUBRID_DATE_LANG environment used in earlier version of CUBRID 9.0 is no longer supported.

The following example shows execution of the database by setting the environment variable CUBRID_CHARSET to "en_US".

--selecting a datetime type value casted from a string in the specified format

SELECT TO_DATETIME('13:10:30 12/25/2008');

 to_datetime('13:10:30 12/25/2008')
=====================================
  01:10:30.000 PM 12/25/2008

SELECT TO_DATETIME('08-Dec-25 13:10:30.999', 'YY-Mon-DD HH24:MI:SS.FF');

 to_datetime('08-Dec-25 13:10:30.999', 'YY-Mon-DD HH24:MI:SS.FF')
=====================================
  01:10:30.999 PM 12/25/2008

SELECT TO_DATETIME('DATE: 12-25-2008 TIME: 13:10:30.999', '"DATE:" MM-DD-YYYY "TIME:" HH24:MI:SS.FF');

 to_datetime('DATE: 12-25-2008 TIME: 13:10:30.999', '"DATE:" MM-DD-YYYY "TIME:" HH24:MI:SS.FF')
=====================================
  01:10:30.999 PM 12/25/2008

The following example shows the case when the system parameter intl_date_lang is "de_DE".

SET SYSTEM PARAMETERS 'intl_date_lang="de_DE"';
SELECT TO_DATETIME('13:10:30.999 25.12.2012');

   to_datetime('13:10:30.999 25.12.2012')
=========================================
  01:10:30.999 PM 12/25/2012

SELECT TO_DATETIME('12/mai/2012 12:10:00 Nachm.','DD/MON/YYYY HH:MI:SS AM', 'de_DE');

   to_datetime('12/mai/2012 12:10:00 Nachm.', 'DD/MON/YYYY HH:MI:SS AM', 'de_DE')
=================================================================================
  12:10:00.000 PM 05/12/2012

Note

When the charset is ISO-8859-1, the language that can be changed in TO_DATETIME function is "ko_KR" and "tr_TR" except "en_US". If the charset is UTF-8, it can be changed to any language supported by CUBRID. For details, see Note in the TO_CHAR().

TO_NUMBER

TO_NUMBER(string[, format])

The TO_NUMBER function interprets a character string based on the number format given as an argument, converts it to a NUMERIC type value, and returns it.

Parameters:
  • string -- Specifies an expression that returns character string. If the value is NULL, NULL is returned.
  • format -- Specifies a format of return value to be converted as NUMBER type. See Number Format. If it is omitted, NUMERIC(38,0) value is returned.
Return type:

NUMERIC

When the format argument is assigned, the string is interpreted according to the specified language. At this time, the language is specified by the intl_number_lang system parameter. If the value of intl_number_lang is not set, the language specified when creating DB is applied.

For example, when the language is one of the European languages, such as "de_DE" and "fr_FR", "." is interpreted as the cipher identifier and "," as the decimal point symbol. When the format parameter specified does not correspond to the given string, an error is returned.

If the format argument is omitted, string is interpreted according to the default output format set by intl_number_lang (see Default Output of Number for Each Language). When the intl_number_lang is not set, the language specified when creating DB is applied.

The following example shows execution of the database by setting the value of system parameter intl_number_lang as "en_US".

SET SYSTEM PARAMETERS 'intl_number_lang="en_US"';

--selecting a number casted from a string in the specified format
SELECT TO_NUMBER('-1234');

 to_number('-1234')
============================================
  -1234

SELECT TO_NUMBER('12345','999999');

 to_number('12345', '999999')
============================================
  12345

SELECT TO_NUMBER('12,345.67','99,999.999');

 to_number('12,345.67', '99,999.999')
======================
  12345.670

SELECT TO_NUMBER('12345.67','99999.999');

 to_number('12345.67', '99999.999')
============================================
  12345.670

The following example shows command execution on the database by setting the value of the intl_number_lang system parameter to "de_DE". In the number output format of most European countries, such as Germany and France, "." is the cipher identifier and "," is the decimal point symbol.

SET SYSTEM PARAMETERS 'intl_number_lang="de_DE"';
SELECT TO_NUMBER('12.345,67','99.999,999');

   to_number('12.345,67', '99.999,999')
======================
  12345.670

TO_TIME

TO_TIME(string[, format[, date_lang_string_literal]])

The TO_TIME function interprets a character string based on the time format given as an argument, converts it to a TIME type value, and returns it. For the format, see Date/Time Format 1.

Parameters:
  • string -- Specifies an expression that returns character string. If the value is NULL, NULL is returned.
  • format -- Specifies a format of return value to be converted as TIME type. See Date/Time Format 1. If the value is NULL, NULL is returned.
  • date_lang_string_literal -- Specifies the language for the input value to be applied.
Return type:

TIME

When the format argument is specified, the string is interpreted according to the specified language. At this time, the language is set by date_lang_string_literal argument. If date_lang_string_literal argument is not set, the language is specified by the intl_date_lang system parameter; if the value of intl_date_lang is not set, the language specified when creating DB is applied. When the format parameter does not correspond to the given string, an error is returned.

For example, when a language is "de_DE" and string is "10:23:00 Nachm.", and format is "HH/MI/SS/AM, it is interpreted as 10:23:00 PM.

When the format argument is omitted, string is interpreted as the CUBRID default format (refer to Recommended Format for Strings in Date/Time Type) and if it fails, string is interpreted as the language format (see the table Default Output Format of Language in the TO_CHAR()) by intl_date_lang. If the value of intl_date_lang is not set, the language is applied by the language specified when creating DB.

For example, when a language is "de_DE", the acceptable strings for TIME type are "HH:MI:SS AM", CUBRID default format and "HH24:MI:SS", "de_DE" default format.

Note

The CUBRID_DATE_LANG environment used in earlier version of CUBRID 9.0 is no longer supported.

The following example shows execution of the database by setting the value of system parameter intl_date_lang as "en_US".

SET SYSTEM PARAMETERS 'intl_date_lang="en_US"';

--selecting a time type value casted from a string in the specified format

SELECT TO_TIME ('13:10:30');

 to_time('13:10:30')
=============================================
  01:10:30 PM

SELECT TO_TIME('HOUR: 13 MINUTE: 10 SECOND: 30', '"HOUR:" HH24 "MINUTE:" MI "SECOND:" SS');

 to_time('HOUR: 13 MINUTE: 10 SECOND: 30', '"HOUR:" HH24 "MINUTE:" MI "SECOND:" SS')
====================================================================================
  01:10:30 PM

SELECT TO_TIME ('13:10:30', 'HH24:MI:SS');

 to_time('13:10:30', 'HH24:MI:SS')
==================================
  01:10:30 PM

SELECT TO_TIME ('13:10:30', 'HH12:MI:SS');

ERROR: Conversion error in date format.

The following example shows the case when the system parameter intl_date_lang is "de_DE".

SET SYSTEM PARAMETERS 'intl_date_lang="de_DE"';
SELECT TO_TIME('13:10:30');

 to_time('13:10:30')
======================
  01:10:30 PM

SELECT TO_TIME('10:23:00 Nachm.', 'HH:MI:SS AM');

 to_time('10:23:00 Nachm.', 'HH:MI:SS AM')
==============================================
  10:23:00 PM

Note

When the charset is ISO-8859-1, the language that can be changed by the system parameter intl_date_lang is "ko_KR" and "tr_TR" except "en_US". If the charset is UTF-8, it can be changed to any language supported by CUBRID. For details, see Note in the TO_CHAR()).

TO_TIMESTAMP

TO_TIMESTAMP(string[, format[, date_lang_string_literal]])

The TO_TIMESTAMP function interprets a character string based on the time format given as an argument, converts it to a TIMESTAMP type value, and returns it. For the format, see Date/Time Format 1.

Parameters:
  • string -- Specifies an expression that returns character string. If the value is NULL, NULL is returned.
  • format -- Specifies a format of return value to be converted as TIMESTAMP type. See Date/Time Format 1. If the value is NULL, NULL is returned.
  • date_lang_string_literal -- Specifies the language for the input value to be applied.
Return type:

TIMESTAMP

When the format argument is specified, the string is interpreted according to the specified language. The language is set by date_lang_string_literal argument. If date_lang_string_literal argument is not set, the language is specified by the intl_date_lang system parameter; if the value of intl_date_lang is not set, the language specified when creating DB is applied.

For example, when a language is "de_DE" and string is "12/mai/2012 12:10:00 Nachm.", and format is "DD/MON/YYYY HH:MI:SS AM", it is interpreted as May 12th, 2012, 12:10:00 AM. When the format parameter specified does not correspond to the given string, an error is returned.

When the format argument is omitted, string is interpreted as the CUBRID default format(refer to Recommended Format for Strings in Date/Time Type) and if it fails, string is interpreted as the language format (see the table Default Output Format of Language in the TO_CHAR()) by intl_date_lang. If the value of intl_date_lang is not set, the language is applied by the language specified when creating DB.

For example, when a language is "de_DE", the acceptable strings for TIMESTAMP type are "HH:MI:SS AM MM/DD/YYYY", CUBRID default format and "HH24:MI:SS DD.MM.YYYY", "de_DE" default format.

The following example shows execution of the database by setting the value of system parameter intl_date_lang as "en_US".

SET SYSTEM PARAMETERS 'intl_date_lang="en_US"';

--selecting a timestamp type value casted from a string in the specified format

SELECT TO_TIMESTAMP('13:10:30 12/25/2008');

 to_timestamp('13:10:30 12/25/2008')
======================================
  01:10:30 PM 12/25/2008

SELECT TO_TIMESTAMP('08-Dec-25 13:10:30', 'YY-Mon-DD HH24:MI:SS');

 to_timestamp('08-Dec-25 13:10:30', 'YY-Mon-DD HH24:MI:SS')
======================================
  01:10:30 PM 12/25/2008

SELECT TO_TIMESTAMP('YEAR: 2008 DATE: 12-25 TIME: 13:10:30', '"YEAR:" YYYY "DATE:" MM-DD "TIME:" HH24:MI:SS');

 to_timestamp('YEAR: 2008 DATE: 12-25 TIME: 13:10:30', '"YEAR:" YYYY "DATE:" MM-DD "TIME:" HH24:MI:SS')
======================================
  01:10:30 PM 12/25/2008

The following example shows the case when the system parameter intl_date_lang is "de_DE".

SET SYSTEM PARAMETERS 'intl_date_lang="de_DE"';
SELECT TO_TIMESTAMP('13:10:30 25.12.2008');

   to_timestamp('13:10:30 25.12.2008')
======================================
  01:10:30 PM 12/25/2008

SELECT TO_TIMESTAMP('10:23:00 Nachm.', 'HH12:MI:SS AM');

   to_timestamp('10:23:00 Nachm.', 'HH12:MI:SS AM')
===================================================
  10:23:00 PM 08/01/2012

Note

When the charset is ISO-8859-1, the language that can be changed by the system parameter intl_date_lang is "ko_KR" and "tr_TR" except "en_US". If the charset is UTF-8, it can be changed to any language supported by CUBRID. For details, see Note in the TO_CHAR()).